if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsDNSeedNewProtocolsStaff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsDNSeedNewProtocolsStaff]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			spFdsDNSeedNewProtocolsStaff
* Purpose:		Using table FdsProtocolStagingHumProtStaff, 
*               populate Protocols tables
*				with IDs for all new staffing information
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.spFdsDNSeedNewProtocolsStaff
AS
BEGIN


BEGIN TRANSACTION spFdsDNSeedNewProtocolsStaff

	

	SET NOCOUNT ON

	DECLARE	@_svcEntityStaffedPersonSeed  int
	DECLARE @_subsprocStatus AS BIT
	DECLARE	@_sql	varchar(1000)
	DECLARE	@SUCCESS	int
	DECLARE	@FAILURE	int
	DECLARE @_NOTUPDATE AS BIT

	SET	@SUCCESS = 1
	SET	@FAILURE = 0
	SET @_NOTUPDATE = 0	

	
	-- Insert into SvcEntity_AdmStaffedPerson_Map and PrtStaffedPerson 
	SET IDENTITY_INSERT SvcEntity_AdmStaffedPerson_Map OFF

	INSERT	into SvcEntity_AdmStaffedPerson_Map(SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId, DateCreated)
	SELECT	SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId, dtInserted
	FROM	FdsProtocolStagingHumProtStaff
	WHERE IsUpdate = @_NOTUPDATE
	
	 IF @@ERROR > 0 GOTO ERROR_HANDLER	

	INSERT INTO PrtStaffedPerson(SvcEntityStaffedPersonMapId, HasCertified, FwkOrganizationId)
	SELECT sam.Id, 0, pss.FwkPrimaryOrgId
	FROM SvcEntity_AdmStaffedPerson_Map sam
	INNER JOIN FdsProtocolStagingHumProtStaff pss
	ON sam.svcEntityId = pss.SvcEntityId
	AND sam.AdmPersonId = pss.AdmPersonId
	AND sam.AdmStaffedPersonTypeId = pss.AdmStaffedPersonTypeId
	WHERE IsUpdate = @_NOTUPDATE
	
	 IF @@ERROR > 0 GOTO ERROR_HANDLER
   
--- grant access to the study staff other than PIs
  		
	DECLARE @_READ_ACCESS_BITMASK AS INTEGER
	DECLARE @_EXPLICIT_PROVISION_BITVALUE AS INTEGER
	
		
	SET @_EXPLICIT_PROVISION_BITVALUE = 2

	SET @_READ_ACCESS_BITMASK = (SELECT dbo.fnGetAccessRightBitvalueByName ('Read'))
	
	

	DECLARE curStudyStaffAccessRights CURSOR LOCAL FAST_FORWARD FOR
	SELECT pse.FwkPrincipalId, pp.FwkResourceId
    	FROM FdsProtocolStagingHumProtStaff pse
	INNER JOIN PrtProtocol pp
	ON pp.Id = pse.PrtProtocolId 
	WHERE pse.FwkPrincipalId is not null 
	AND pse.IsUpdate = @_NOTUPDATE

	DECLARE @_resourceId AS INTEGER
	DECLARE @_principalId AS INTEGER

	OPEN curStudyStaffAccessRights 
	FETCH NEXT FROM curStudyStaffAccessRights INTO @_principalId ,  @_resourceId

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXEC @_subsprocStatus = spFwkNGrantAccess  @_principalId ,  @_resourceId,
					  @_EXPLICIT_PROVISION_BITVALUE, @_READ_ACCESS_BITMASK

		
		IF @_subsprocStatus = @FAILURE GOTO ERROR_HANDLER

		FETCH NEXT FROM curStudyStaffAccessRights INTO @_principalId,  @_resourceId
		
	END

	
	-- close and deallocate cursor
	CLOSE curStudyStaffAccessRights
	DEALLOCATE curStudyStaffAccessRights 



	--- Create Contacts for records that have isContact (bGetsMail) set to 1. 
	Insert into SvcEntity_AdmContact_Map(SvcEntityId, AdmPersonId, AdmContactTypeId, DateCreated)
	SELECT svcEntityId, AdmPersonId, 5 as AdmContactTypeId, getDate() as DateCreated
	FROM  FdsProtocolStagingHumProtStaff psc
	WHERE bGetsMail = 1
 	AND NOT EXISTS ( SELECT 1 FROM SvcEntity_AdmContact_Map 
				  WHERE SvcEntityId = psc.SvcEntityId
				    AND AdmPersonId = psc.AdmPersonId
				)
	AND psc.IsUpdate = @_NOTUPDATE
	
	 IF @@ERROR > 0 GOTO ERROR_HANDLER

	-- Remove contacts that have their bGetsmail set to 0
	DELETE SvcEntity_AdmContact_Map 
	FROM SvcEntity_AdmContact_Map sam
	INNER JOIN FdsProtocolStagingHumProtStaff psc
	ON sam.SvcEntityId = psc.SvcEntityId 
	 AND sam.AdmPersonId = psc.AdmPersonId 
	WHERE psc.bGetsMail = 0
	AND psc.IsUpdate = @_NOTUPDATE
	

	 IF @@ERROR > 0 GOTO ERROR_HANDLER
	
				
	SET NOCOUNT OFF
	COMMIT TRANSACTION  spFdsDNSeedNewProtocolsStaff
	RETURN @SUCCESS
	
	ERROR_HANDLER: 
	  SET NOCOUNT OFF
	  ROLLBACK TRANSACTION spFdsDNSeedNewProtocolsStaff
	 RETURN @FAILURE



END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF